package org.qf.dao.impl;


import com.mysql.cj.util.StringUtils;
import com.sun.javafx.iio.common.SmoothMinifier;
import org.qf.dao.SmbmsUserDao;
import org.qf.pojo.SmbmsUser;
import org.qf.utils.BaseDao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SmbmsUserDaoImpl extends BaseDao implements SmbmsUserDao {


    //通过用户编码查询用户信息
    @Override
    public SmbmsUser queryUserByUserCode(String userCode) throws Exception {
        SmbmsUser  smbmsUser=new SmbmsUser();
        String sql="select  * from smbms_user where userCode=?";
        Object[] params={userCode};
        ResultSet resultSet = executeSQL(sql, params);
        while(resultSet.next()){
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setUserPassword(resultSet.getString("userPassword"));
        }

        return smbmsUser;
    }

    @Override
    public int upDatepw(Long oldPassword,Long newPassword) throws Exception {
        int count=0;
        //String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=?";
        String sql="update smbms_user set userPassword=? where userPassword=?";
        Object[] params={newPassword,oldPassword};
        count=executeUpdate(sql,params);
        return count;
    }

    //查询用户信息并支持模糊查询
    @Override
    public List<SmbmsUser> queryUserByUserName(String username,int userRole,int currentPageNo,int pageSize) throws Exception {
        List<SmbmsUser> list=new ArrayList<SmbmsUser>();
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if(!StringUtils.isNullOrEmpty(username)){
            sql.append(" and u.userName like ?");
            obj.add("%"+username+"%");
        }
        if(userRole>0){
            sql.append(" and u.userRole=?");
            obj.add(userRole);
        }
        //分页
        sql.append(" order by u.creationDate desc limit ?,?");
        obj.add((currentPageNo-1)*pageSize);
        obj.add(pageSize);
        Object[] params=obj.toArray();
        System.out.println(sql.toString()+"========================");
        System.out.println(params+"=====================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next()){
            SmbmsUser smbmsUser=new SmbmsUser();
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
            //把对象放在集合中
            list.add(smbmsUser);
        }

        return list;
    }

    @Override
    public int getSmbmsUserCount(String username,int userRole) throws Exception {
        int count=0;
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select count(*) from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if(!StringUtils.isNullOrEmpty(username))
        {
            sql.append(" and u.userName like ?");
            obj.add("%"+username+"%");
        }if(userRole>0)
        { sql.append(" and u.userRole=?");
        obj.add(userRole);
        }
        Object[] params=obj.toArray();
        System.out.println(sql+"==========================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next())
        { count=resultSet.getInt(1); }
        return count;
    }

    @Override
    public SmbmsUser queryUserById(Long id) throws Exception {
        SmbmsUser smbmsUser=new SmbmsUser();
        String sql="select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id and u.id=?";
        Object[] params={id};
        ResultSet resultSet = executeSQL(sql, params);
        while(resultSet.next())
        {
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setAddress(resultSet.getString("address"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
        }
        return smbmsUser;
    }

    @Override
    public int updateUser(SmbmsUser smbmsUser) throws Exception {
        int count=0;
        String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=?";
        Object[] params= {smbmsUser.getUserName(),smbmsUser.getGender(),smbmsUser.getBirthday(),smbmsUser. getPhone(),smbmsUser.getAddress(),smbmsUser.getUserRole(),smbmsUser.getId()};
        count= executeUpdate(sql, params);
        return count;
    }

    @Override
    public int addUser(SmbmsUser smbmsUser) throws Exception {
        int count=0;
        String sql="insert into smbms_user(userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate)values(?,?,?,?,?,?,?,?,?,?)";
        Object[] params= {smbmsUser.getUserCode(),smbmsUser.getUserName(),smbmsUser.getUserPassword(),smbmsUser.getGender(),smbmsUser.getBirthday(),smbmsUser.getPhone(),smbmsUser.getAddress (),smbmsUser.getUserRole(),smbmsUser.getCreatedBy(),smbmsUser.getCreationDate()};
        count = executeUpdate(sql, params);
        return count;
    }

    @Override
    public int deleteUser(Long id) throws Exception {
        int count=0;
        String sql="delete from smbms_user where id=?";
        Object[] params={id};
        count = executeUpdate(sql, params);
        return count;
    }
}
